﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Collections;
using System.Reflection;

using BiaoQianPrint.Model;
using BiaoQianPrint.Tools;
using BiaoQianPrint.Excel;
namespace BiaoQianPrint.YeWu
{
    //如果有导入失败的行，提示一下。


    public partial class frmImportExcel_SH : Form
    {
        
        DataTable dt;
        IExcelImport excelManager=new ExcelImportImpl();

        public frmImportExcel_SH()
        {
            InitializeComponent();

        }
        #region 保存数据
        private void btnToDB_Click(object sender, EventArgs e)
        {
            //创建一个  Mod_SHData的对象
            
            Ctrl_SHData objSH = new Ctrl_SHData("");

            System.Text.StringBuilder sb = new StringBuilder();
            if (dt != null)
            {
                foreach (DataRow row in dt.Rows)
                {
                    //必须车架号和上面的是同一个； 同一辆车，项目不能重复，然后 导入即可
                    //mSHDate = Convert.ToDateTime(txtSHDate.Text.ToString()).ToString("yyyy-MM-dd");
                    if (string.IsNullOrEmpty(row[0].ToString().Trim()) == false)
                    {
                        //此处验证。非法数据都提示出来。必须处理后再执行。
                        if (string.IsNullOrEmpty(row[2].ToString().Trim()) == true | string.IsNullOrEmpty(row[1].ToString().Trim()) == true)
                        {
                            sb.AppendLine(row["PN"].ToString() + "存在PN号或DC空的情况！\r\n");
                        }
                        else if (row[2].ToString().Trim().Length != 6)
                        {
                            sb.AppendLine(row["PN"].ToString() + "存在DC位数不是5位的情况！\r\n");
                        }
                        int pSHNum = CommonClass.GetIntFromString(row[3].ToString());
                        if (pSHNum == 0)
                        {
                            sb.Append(row["PN"].ToString() + "收获数量不合法！\r\n");
                        }
                        //验证通过的先保存。
                        string pSHDate=DateTime.Parse(row[0].ToString()).ToString("yyyy-MM-dd");
                        
                        objSH.AddItem(pSHDate, row[1].ToString(), row[2].ToString(), pSHNum);
                    }
                }
                
                if (sb.Length > 0)
                {
                    MessageBox.Show(sb.ToString());
                    MessageBox.Show("请改正错误后重新导入。");
                }
                else
                {
                    string mFileName = "SH_" + CommonClass.GetSysTime().ToString("yyyy-MM-dd_hh-mm-ss") + "_" + objSH.SHDate + "_" + objSH.ShSum.ToString() + ".txt";
                    objSH.SaveDataToFile(mFileName);
                }
            }
            objSH = null;
        }

        
        #endregion

        #region 打开文件   同时把 Sheet列表读取出来。
        private void btnOpen_Click(object sender, EventArgs e)
        {
            openFileDlg.Filter = "Excel2007(*.xlsx)|*.xlsx|Excel2003(*.xls)|*.xls";
            if (openFileDlg.ShowDialog() == DialogResult.OK)
            {
                filePathBox.Text = openFileDlg.FileName;
                DataTable dtSheets= excelManager.GetSheetList(filePathBox.Text.ToString());
                //GetSheetList(filePathBox.Text.ToString());

                cblList.Items.Clear();
                foreach (DataRow dr in dtSheets.Rows)
                {
                    // 用下面方法无效的sheet
                    if (dr[2].ToString().Contains("$") && !dr[2].ToString().EndsWith("$"))
                    {
                        continue;
                    }
                    cblList.Items.Add(dr[2].ToString());
                }
                if (cblList.Items.Count > 0) cblList.SelectedIndex = 0;
            }
        }
        
        #endregion

        #region 导入
        private void btnDaoRu_Click(object sender, EventArgs e)
        {
            string strSheetName = cblList.SelectedItem.ToString();
            dt = excelManager.getDataTableFromExcel(strSheetName);
            FormatDt();
            grd.DataSource = dt;
        }
        
        
        #endregion

        #region 导出数据
        private void btnExport_Click(object sender, EventArgs e)
        {
            string saveFileName = "";
            SaveFileDialog saveDialog = new SaveFileDialog();
            saveDialog.DefaultExt = "xlsx|xls";
            saveDialog.Filter = "Excel(*.xlsx)|*.xlsx|Excel(*.xls)|*.xls";
            
            if (saveDialog.ShowDialog() == DialogResult.OK)
            {
                saveFileName = saveDialog.FileName;
                IExcelExport excelManager = new ExcelExportImpl();
                string stringErr = excelManager.ExportExcelMaster(dt, saveFileName);
                if (stringErr.Length > 0)
                {
                    MessageBox.Show("导出Excel时出错，错误描述：" + stringErr);
                }
                else
                {
                    #region 打开保存成功的文件
                    try
                    {
                        System.Diagnostics.Process.Start(saveFileName);//打开导出的文件

                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                    #endregion
                }
            }
        }
        #endregion

        #region FormatDt()  去掉 第一列空的行
        private void FormatDt()
        {
            if (dt == null) return;
            //按照日期，从grid表格中 过滤掉其他日期的：
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (dt.Rows[i][0] == null)
                {
                    dt.Rows[i].Delete();
                }
                else
                {
                    if (dt.Rows[i][0].ToString().Trim().Length == 0)
                    {
                        dt.Rows[i].Delete();
                    }
                }
            }
            dt.AcceptChanges();
            SumInfo();
            this.grd.DataSource = dt;
        }
        #endregion

        private void btnGuoLvByDate_Click(object sender, EventArgs e)
        {
            if (dt == null) {
                MessageBox.Show("请先读取数据！");
                return;
            }
            //按照日期，从grid表格中 过滤掉其他日期的：
            for(int i=0;i<dt.Rows.Count;i++)
            {
                if (DateTime.Parse(dt.Rows[i][0].ToString()).ToString("yyyy-MM-dd") != this.dtpGuoLv.Value.ToString("yyyy-MM-dd"))
                {
                    dt.Rows[i].Delete();
                }
            }
            dt.AcceptChanges();
            SumInfo();
            this.grd.DataSource = dt;
        }

        #region 统计行数，和数量
        private void SumInfo()
        {
            int mCount = 0;
            double mSum = 0;
            int tmpNum = 0;
            string row3 = "";
            foreach (DataRow row in dt.Rows)
            {
                if (string.IsNullOrEmpty(row[0].ToString().Trim()) == false)
                {
                    mCount++;
                    row3 = row[3].ToString();
                    int.TryParse(row3, out tmpNum);
                    mSum = mSum + tmpNum;
                }
            }
            lblRowCount.Text = mCount.ToString();
            this.lblReceiveNumHJ.Text = mSum.ToString();
        }
        #endregion

    }
}
